package com.raja.servingStation.dao;

import com.alibaba.fastjson.JSONObject;
import com.raja.servingStation.common.util.IdGenerator;
import com.raja.servingStation.datasources.RepairSql2oTemp;
import com.raja.servingStation.entity.CarInfoEx;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.sql2o.Connection;
import org.sql2o.Query;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import java.util.regex.Pattern;


/**
 * 首保工单Dao
 * on 2017/9/30.
 */
@Repository
public class FirstInsuranceOrderDao {
    private static final Logger log = LoggerFactory.getLogger(FirstInsuranceOrderDao.class);

    @Autowired
    private RepairSql2oTemp sql2oTemp;

    @Autowired
    private IdGenerator idGenerator;


    public Object addFirstInsuranceOrder(JSONObject body) throws Exception{
        String sql = "insert into t_first_insurance_order(ORDER_CODE,CAR_NO,CAR_ID,CAR_OWNER,CAR_OWNER_ADDRESS,USER_NAME,USERE_ID_CARD," +
                "USER_PHONE,USER_TELEPHONE,FIRST_INSURANCE_DATE,CURRENT_TOTAL_KM,INSURANCE_REAMRK,ENGINEER_ID,REPAIR_ID,SETTLEMENT_TYPE,INSURANCE_STATE) " +
                "values(:orderCode,:carNo,:carId,:carOwner,:carOwnerAdd,:userName,:userIdCard,:userPhone,:userTelephone,:firstInsuranceDate," +
                ":currentTotalKm,:insuranceRemark,:engineerId,:repairId,2,1)";
        log.debug(sql);
        try (Connection con = sql2oTemp.getSql2o().open()) {
            Query query = con.createQuery(sql);
            Query query2 = con.createQuery("select max(FIO_ID) from t_first_insurance_order ");
            query.addParameter("orderCode",idGenerator.nextVal(1,2));
            query.addParameter("carNo", body.getString("carNo"));
            query.addParameter("carId", body.getString("carId"));
            query.addParameter("carOwner", body.getString("carOwner"));
            query.addParameter("carOwnerAdd", body.getString("carOwnerAdd"));
            query.addParameter("userName", body.getString("userName"));

            if(Pattern.matches("(^\\d{18}$)|(^\\d{15}$)", body.getString("userIdCard"))){
                query.addParameter("userIdCard", body.getString("userIdCard"));
            }else {
                return "身份证不合法！";
            }
            if(Pattern.matches("^((17[0-9])|(14[0-9])|(13[0-9])|(15[^4,\\D])|(18[0,5-9]))\\d{8}$", body.getString("userPhone"))){
                query.addParameter("userPhone", body.getString("userPhone"));
            }else {
                return "手机号不合法！";
            }
            if(Pattern.matches("^^[1-9]{1}[0-9]{5,8}$", body.getString("userTelephone"))){
                query.addParameter("userTelephone", body.getString("userTelephone"));
            }else {
                return "座机号不合法！";
            }
            Date firstInsuranceDate = new SimpleDateFormat("yyyy-MM-dd").parse(body.getString("firstInsuranceDate"));
            query.addParameter("firstInsuranceDate",firstInsuranceDate);
            query.addParameter("currentTotalKm", body.getBigDecimal("currentTotalKm"));
            query.addParameter("insuranceRemark", body.getString("insuranceRemark"));
            query.addParameter("engineerId", body.getLong("engineerId"));
            query.addParameter("repairId", body.getInteger("repairId"));
            query.executeUpdate();
            return query2.executeScalar();
        }
    }

    public CarInfoEx getCarInfoByCarNo(String carNo) throws Exception {
        StringBuilder sql = new StringBuilder();
        sql.append(" select t1.CAR_VIN carVin,t1.CAR_MODEL carTypeCode,t2.CHASSIS_FACTORY chassisFactory,t2.BODY_BRAND bodyBrand, " +
                " t2.CAR_TYPE carBrandType,t1.MOTOR_NUMBER motorNumber,t1.COMPANY_NAME companyName,t1.PRODUCT_TIME pdTime,t1.KP_TIME kpTIME,t1.TOTAL_KM totalKm " +
                "   from t_car t1 left join t_car_model  t2 on t1.CAR_MODEL = t2.MODEL_CODE  where  t1.PLATE_NO = :carNo ");
        log.debug(sql.toString());
        try (Connection con = sql2oTemp.getSql2o().open()) {
            Query query = con.createQuery(sql.toString());
            query.addParameter("carNo",carNo);
            return query.executeAndFetchFirst(CarInfoEx.class);
        }
    }

    public Long getCarNoInSbOrders1(String carNo) throws Exception {
        String sql = " select count(FIO_ID) from t_first_insurance_order where CAR_NO=:carNo ";
        log.debug(sql);
        try (Connection con = sql2oTemp.getSql2o().open()) {
            Query query = con.createQuery(sql);
            query.addParameter("carNo",carNo);
            return (Long) query.executeScalar();
        }
    }
    public Long getCarNoInSbOrders2(String carNo) throws Exception {
        String sql = " select count(FIO_ID) from t_first_insurance_order where CAR_NO=:carNo and INSURANCE_STATE = 0 ";
        log.debug(sql);
        try (Connection con = sql2oTemp.getSql2o().open()) {
            Query query = con.createQuery(sql);
            query.addParameter("carNo",carNo);
            return (Long) query.executeScalar();
        }
    }

    public Object submitSbOrder(JSONObject body) throws Exception {
        String sql = "insert into t_first_insurance_order(ORDER_CODE,CAR_NO,CAR_ID,CAR_OWNER,CAR_OWNER_ADDRESS,USER_NAME,USERE_ID_CARD," +
                "USER_PHONE,USER_TELEPHONE,FIRST_INSURANCE_DATE,CURRENT_TOTAL_KM,INSURANCE_REAMRK,ENGINEER_ID,REPAIR_ID,SETTLEMENT_TYPE,INSURANCE_STATE) " +
                "values(:orderCode,:carNo,:carId,:carOwner,:carOwnerAdd,:userName,:userIdCard,:userPhone,:userTelephone,:firstInsuranceDate," +
                ":currentTotalKm,:insuranceRemark,:engineerId,:repairId,2,0)";
        log.debug(sql);
        try (Connection con = sql2oTemp.getSql2o().open()) {
            Query query = con.createQuery(sql);
            Query query2 = con.createQuery("select max(FIO_ID) from t_first_insurance_order ");
            query.addParameter("orderCode",idGenerator.nextVal(1,2));
            query.addParameter("carNo", body.getString("carNo"));
            query.addParameter("carId", body.getString("carId"));
            query.addParameter("carOwner", body.getString("carOwner"));
            query.addParameter("carOwnerAdd", body.getString("carOwnerAdd"));
            query.addParameter("userName", body.getString("userName"));
            query.addParameter("userIdCard", body.getString("userIdCard"));
            query.addParameter("userPhone", body.getString("userPhone"));
            query.addParameter("userTelephone", body.getString("userTelephone"));
            Date firstInsuranceDate = new SimpleDateFormat("yyyy-MM-dd").parse(body.getString("firstInsuranceDate"));
            query.addParameter("firstInsuranceDate",firstInsuranceDate);
//            query.addParameter("firstInsuranceDate",new Timestamp(body.getDate("firstInsuranceDate").getTime()));
            query.addParameter("currentTotalKm", body.getBigDecimal("currentTotalKm"));
            query.addParameter("insuranceRemark", body.getString("insuranceRemark"));
            query.addParameter("engineerId", body.getLong("engineerId"));
            query.addParameter("repairId", body.getInteger("repairId"));
            query.executeUpdate();
            return query2.executeScalar();
        }
    }

    public Object updateSbOrderState(JSONObject body) {
        String sql = "update t_first_insurance_order set INSURANCE_STATE = 1 where CAR_NO=:carNo ";
        log.debug(sql);
        Map<String,Object> m = new HashMap();
        try (Connection con = sql2oTemp.getSql2o().open()) {
            Query query = con.createQuery(sql);
            query.addParameter("carNo",body.getString("carNo"));
            query.executeUpdate();
            m.put("state", Boolean.TRUE);
            return m;
        }catch (Exception e){
            log.debug(e.getMessage());
            m.put("state", Boolean.FALSE);
            return m;
        }
    }

}
